﻿/*
 *  Copyright 2009 Alessandro Pilotti
 *
 *  This program is free software; you can redistribute it and/or modify
 *  it under the terms of the GNU Lesser General Public License as published by
 *  the Free Software Foundation; either version 2.1 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU Lesser General Public License for more details.
 *
 *  You should have received a copy of the GNU Lesser General Public License
 *  along with this program; if not, write to the Free Software
 *  Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA 
 */

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Linq;


namespace AlexPilotti.L2SQLDataModel.SchemaInfo
{
    public class MSSQL05DBSchemaInfo : MSSQLBaseDBSchemaInfo
    {
        internal MSSQL05DBSchemaInfo(string connStr)
            : base(connStr)
        {
        }

        protected override string GetCreateTempTableSql(string objectTypesSql)
        {
            // Avoid system and databasase tools support objects (e.g.: "sysdiagrams")
            return @"select o.object_id, SCHEMA_NAME(o.schema_id) as schema_name, o.name, o.type into #Objects from sys.objects o " +
                    "left outer join sys.extended_properties ep on ep.major_id = o.object_id and ep.minor_id = 0 " +
                    "and ep.name = 'microsoft_database_tools_support' " +
                    "where ep.major_id is null and o.is_ms_shipped = 0 and o.type IN (" + objectTypesSql + ") " +
                    "order by o.type, SCHEMA_NAME(o.schema_id), o.name";
        }

        protected override string GetParametersInfoSql()
        {
            // Scalar funtions return parameters are marked as output parameters with an empty string as name
            return "SELECT o.schema_name, " +
                    "sp.name AS [object_name], " +
                    "sp.type AS object_type, " +
                    "param.name AS parameter_name, " +
                    "param.parameter_id AS [ID], " +
                    "param.is_output AS [IsOutputParameter], " +
                    "CASE WHEN usrt.is_table_type = 1 THEN N'structured' ELSE N'' END AS [UserType], " +
                    "usrt.name AS [DataType], " +
                    "sparam.name AS [DataTypeSchema], " +
                    "ISNULL(baset.name, N'') AS [SystemType], " +
                    "CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND param.max_length <> -1 THEN param.max_length/2 ELSE param.max_length END AS int) AS [Length], " +
                    "CAST(param.precision AS int) AS [NumericPrecision], " +
                    "CAST(param.scale AS int) AS [NumericScale] " +
                    "FROM " +
                    "sys.objects AS sp " +
                    "INNER JOIN #Objects o ON sp.object_id = o.object_id and o.type IN (" + procFuncTypesSql+ ") " +
                    "LEFT OUTER JOIN sys.parameters AS param ON param.object_id=sp.object_id " +
                    "LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = param.user_type_id " +
                    "LEFT OUTER JOIN sys.schemas AS sparam ON sparam.schema_id = usrt.schema_id " +
                    "LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = param.system_type_id and baset.user_type_id = baset.system_type_id) or " +
                    "((baset.system_type_id = param.system_type_id) and (baset.user_type_id = param.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1)) " +
                    "ORDER BY " +
                    "o.schema_name, sp.name, [ID]";
        }


        protected override string GetColumnsInfoSql()
        {            
/*
            string sql = @"if OBJECT_ID('tempdb..#Objects') IS NOT NULL DROP TABLE #Objects;" +
                        "SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE INTO #Objects FROM INFORMATION_SCHEMA.TABLES " +
                        "WHERE ISNULL(OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMSShipped'), 0) = 0 AND TABLE_TYPE = 'BASE TABLE' " +
                        "ORDER BY TABLE_NAME;";
 */ 

            return  @"SELECT " +
                    "t.schema_name AS schema_name, " +
                    "t.name AS [object_name], " +
                    "clmns.name AS Column_Name, " +
                    "clmns.column_id AS ID, " +
                    "usrt.name AS DataType, " +
                    "sclmns.name AS DataTypeSchema, " +
                    "ISNULL(baset.name, N'') AS SystemType, " +
                    "CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS Length, " +
                    "CAST(clmns.precision AS int) AS [NumericPrecision], " +
                    "CAST(clmns.scale AS int) AS [NumericScale], " +
                    "clmns.is_nullable AS [Nullable], " +
                    "clmns.is_identity AS [Identity], " +
                    "CAST(ISNULL(ic.seed_value,0) AS bigint) AS [IdentitySeed], " +
                    "CAST(ISNULL(ic.increment_value,0) AS bigint) AS [IdentityIncrement], " +
                    "clmns.is_computed AS [Computed] " +
                    "FROM " +
                    "#Objects AS t " +
                    "INNER JOIN sys.columns AS clmns ON clmns.object_id = t.object_id " +
                    "LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id " +
                    "LEFT OUTER JOIN sys.schemas AS sclmns ON sclmns.schema_id = usrt.schema_id " +
                    "LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id) or " +
                    "((baset.system_type_id = clmns.system_type_id) and (baset.user_type_id = clmns.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1))  " +
                    "LEFT OUTER JOIN sys.identity_columns AS ic ON ic.object_id = clmns.object_id and ic.column_id = clmns.column_id " +
                    "ORDER BY " +
                    "schema_name, object_name, Id";
        }
    }
}
